package com.ruoyi.common.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.ruoyi.common.utils.bean.BeanUtils;
import lombok.Data;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.ValidatorFactory;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors;

/**
 * EasyExcel工具类
 *
 * @author lingn
 * @create 2021-01-18
 */
@Slf4j
public class EasyExcelUtil {

    /**
     * 读操作
     */

    /**
     * 读取excel的第一个sheet
     *
     * @param file
     * @param clazz excel实体类
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcel(File file, Class clazz) {
        List<T> list = new ArrayList<>();
        EasyExcel.read(file, clazz, new AnalysisEventListener<T>() {
            @Override
            public void invoke(T t, AnalysisContext analysisContext) {
                list.add(t);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            }
        }).sheet().doRead();
        return list;
    }

    /**
     * 大文件读取，分批读取
     *
     */
    public static <T> List<T> readBigExcel(InputStream file, Class clazz, int step){
        List<T> resList = new ArrayList<>();
        List<T> list = new ArrayList<>();
        EasyExcel.read(file, clazz, new AnalysisEventListener<T>() {
            @Override
            public void invoke(T t, AnalysisContext analysisContext) {
                list.add(t);
                if (list.size() >= step) {
                    resList.addAll(list);
                    list.clear();
                }
                resList.addAll(list);
                list.clear();
            }
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("invoke");
            }
        }).sheet().doRead();
        return resList;
    }

    /**
     * 写操作
     */

    /**
     * 指定一个class写到第一个sheet
     *
     * @param fileName
     * @param clazz
     * @param sheetName
     * @param data
     */
    public static void simpleWrite(String fileName, Class clazz, String sheetName, List data) {
        EasyExcel.write(fileName, clazz).sheet(sheetName).doWrite(data);
    }

    /**
     * 指定一个class写到第一个sheet
     *
     * @param fileName
     * @param clazz
     * @param sheetName
     * @param data
     */
    public static void simpleWrite2(String fileName, Class clazz, String sheetName, List data) {
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(fileName, clazz).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(data, writeSheet);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 根据参数忽略指定字段只写入剩下列
     *
     * @param fileName
     * @param clazz
     * @param sheetName
     * @param data
     * @param excludes  要忽略的字段
     */
    public static void excludeWrite(String fileName, Class clazz, String sheetName, List data, String... excludes) {
        Set<String> excludeColumnFiledNames = new HashSet<>(Arrays.asList(excludes));
        EasyExcel.write(fileName, clazz).excludeColumnFiledNames(excludeColumnFiledNames).sheet(sheetName)
                .doWrite(data);
    }

    /**
     * 根据参数指定字段写入列
     *
     * @param fileName
     * @param clazz
     * @param sheetName
     * @param data
     * @param includes  要写入的字段
     */
    public static void includeWrite(String fileName, Class clazz, String sheetName, List data, String... includes) {
        Set<String> includeColumnFiledNames = new HashSet<>(Arrays.asList(includes));
        EasyExcel.write(fileName, clazz).includeColumnFiledNames(includeColumnFiledNames).sheet(sheetName)
                .doWrite(data);
    }

    /**
     * 使用流写excel
     *
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public static void writeExcel(HttpServletResponse response, List data, String fileName, String sheetName, Class clazz) throws Exception {
        // 头部策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        // 字体
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setWrapped(true);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置内容靠中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 这个策略是 头是头的样式 内容是内容的样式其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
    }

    /**
     * web
     */

    /**
     * 设置OutputStream
     *
     * @param fileName
     * @param response
     * @return
     * @throws Exception
     */
    public static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "no-store");
        response.setHeader("Cache-Control", "max-age=0");
        return response.getOutputStream();
    }

    /**
     * 文件下载
     *
     * @param response
     * @param excelName
     * @param sheetName
     * @param clazz
     * @param data
     * @throws IOException
     */
    public static void download(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode(excelName, StandardCharsets.UTF_8.toString()).replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);

    }


    public static void download(HttpServletResponse response,Class clazz,List  list,String path,String  excelName ) throws IOException {

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyExcel没有关系
        String fileName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        File file = new File(path+File.separator+excelName+".xlsx");
        // 如果不用模板的方式导出的话，是doWrite
        EasyExcel.write(response.getOutputStream(), clazz).withTemplate(file).sheet().needHead(false).doWrite(list);


    }

    /** 下载数据 */
    public static void downloadData(HttpServletResponse response,Class clazz,List  list,String path,String  excelName ) throws IOException {

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyExcel没有关系
        String fileName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        File file = new File(path + File.separator + excelName + ".xlsx");
        // 填充模板用doFill，模板文件{field}为单个对象数据，{.field}为列表数据
        EasyExcel.write(response.getOutputStream(), clazz).withTemplate(file).sheet().doFill(list);


    }



    /**
     * 文件上传
     *
     * @param file
     * @param clazz
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T> List<T> upload(MultipartFile file, Class clazz) throws IOException {
        List<T> list = new ArrayList<>();
        EasyExcel.read(file.getInputStream(), clazz, new AnalysisEventListener<T>() {
            @Override
            public void invoke(T t, AnalysisContext analysisContext) {
                list.add(t);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            }
        }).sheet().doRead();
        return list;
    }


    /**
     *
     * @param file
     * @param clazz
     * @param headRowNum
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T> List<T>  upload (MultipartFile file,Class clazz,Integer headRowNum) throws IOException {

        List<T> list = new ArrayList<>();

        EasyExcel.read(file.getInputStream(), clazz, new AnalysisEventListener<T>() {
            @Override
            public void invoke(T t, AnalysisContext analysisContext) {
                list.add(t);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            }

            @Override
            public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                log.info("读取到表头信息");
                for (Integer key :headMap.keySet()){
                    log.info("key:"+key);
                    log.info("value:"+headMap.get(key));
                }

            }
        }).headRowNumber(headRowNum).sheet().doRead();

        return list;
    }


    /**
     *导入并校验格式与数据
     * @param <T>
     * @param in
     * @param t
     * @param businessValidator
     * @param headNumber
     * @param headMap
     * @return
     */
    public static <T extends BaseExcelDto> ImportResult importExcelAndValidate(InputStream in , T t, BusinessValidator businessValidator,
                                                                               int headNumber, Map<Integer,String> headMap){
        ExcelModelListener<T> excelModelListener = new ExcelModelListener<>(t.getClass(),headMap,headNumber);
        EasyExcel.read(in,t.getClass(),excelModelListener).sheet().headRowNumber(headNumber).doRead();
        List<T> successList = excelModelListener.successList;
        //调用校验逻辑校验读取数据
        ImportResult<T> result = businessValidator.validate(successList);
        if (result == null){
            result =  new ImportResult<>();
            result.setFailedData(excelModelListener.failedList);
            result.setFailedMessage(excelModelListener.failedMsg);
            result.setSuccessData(successList);
        }else{
            if (!StringUtils.isEmpty(result.failedData)){
                result.getFailedData().addAll(excelModelListener.failedList);
                result.getFailedMessage().addAll(excelModelListener.failedMsg);
            }else {
                result.setFailedMessage(excelModelListener.failedMsg);
                result.setFailedData(excelModelListener.failedList);
            }
        }
        return result;
    }


    /**
     * 业务校验接口
     * @param <T>
     */
    public interface BusinessValidator<T extends BaseExcelDto> {

        /**
         * 校验业务
         * @param excelData
         * @return
         */
        ImportResult<T> validate(List<T> excelData);
    }

    /**
     * 导入返回实体
     * @param <T>
     */
    @Data
    public static class ImportResult<T extends BaseExcelDto> {

        private Boolean importStatus;
        private List<T> successData;
        private List<T> failedData;
        private List<String> failedMessage;

        public Boolean getImportStatus(){
            return StringUtils.isEmpty(failedData);
        }
    }

    /**
     * excel基类
     * @param <T>
     */
    @Data
    public static class BaseExcelDto<T> {
        private Integer rowNumber;
    }

    /**
     * 导入数据内部监听器
     * @param <T>
     */
    static class ExcelModelListener<T> extends AnalysisEventListener<T> {

        private Class clazz;
        private Integer headRow;
        private final ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
        private final Validator validator = factory.getValidator();
        private List<String> failedMsg;
        private Map<Integer, String> head;
        private static final String ERR_MSG = "第 %d 行";
        private ArrayList<T> failedList = new ArrayList<>();
        private ArrayList<T> successList = new ArrayList<>();

        public ExcelModelListener(Class z,Map<Integer,String> headMap,Integer headRow){

            this.clazz = z;
            this.head = headMap;
            this.headRow = headRow;
            failedMsg = new ArrayList<>();
        }

        /**
         * validator数据校验
         * @param t
         * @param analysisContext
         */
        @Override
        public void invoke(T t, AnalysisContext analysisContext) {
            int rowNumber = analysisContext.readRowHolder().getRowIndex()+1;
            Method method = BeanUtils.findMethod(t.getClass(),"setRowNumber",Integer.class);
            if (method != null){
                try{
                    method.invoke(t,rowNumber);
                }catch (IllegalAccessException e){
                    e.printStackTrace();
                }catch (InvocationTargetException e){
                    e.printStackTrace();
                }
            }
            Set <ConstraintViolation<T>> validationSet = validator.validate(t);
            if (!StringUtils.isEmpty(validationSet)){
                List<String> messages = validationSet.stream().map(ConstraintViolation::getMessage).collect(Collectors.toList());
                String join = org.apache.commons.lang3.StringUtils.join(messages, "，");
                failedMsg.add(String.format(ERR_MSG, rowNumber) + "：" + join);
                failedList.add(t);
            }else {
                successList.add(t);
            }
        }

        /**
         * 校验表头
         * @param headMap
         * @param context
         */

        @SneakyThrows
        @Override
        public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
            int row = context.readRowHolder().getRowIndex()+1;
            if (headRow.equals(row)){
                if (head == null){
                    head = getIndexNameMap(clazz);
                }
                if (StringUtils.isEmpty(head)){
                    return;
                }
                Set<Integer> ketSet = head.keySet();

                for (Integer key:ketSet){
                    if (StringUtils.isEmpty(headMap.get(key))){
                        throw new ExcelAnalysisException("解析excel出错，请传入正确格式的excel");
                    }
                    if (!headMap.get(key).equals(head.get(key))){
                        throw new ExcelAnalysisException("解析excel出错，请传入正确格式的excel");
                    }
                }
            }

        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            log.info("import data completed!");
        }

        public Map <Integer,String> getIndexNameMap (Class clazz) throws NoSuchFieldException{
            Map<Integer,String> result = new HashMap<>();

            Field field;

            Field [] fields = clazz.getDeclaredFields();
            for (int i=0;i<fields.length;i++){
                field = clazz.getDeclaredField(fields[i].getName());
                field.setAccessible(true);
                ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                if (excelProperty!=null){
                    int index = excelProperty.index();
                    String[] values = excelProperty.value();
                    StringBuilder sb = new StringBuilder();
                    for (String s:values){
                        sb.append(s);
                    }
                    result.put(index,sb.toString());
                }
            }
            return result;
        }

    }











}
